package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class RalationalDataDao {
    //获取数据库中所有数据表的名称
    public ResultSet getTablesName(Connection conn) throws Exception{
        String sql="select tablename from pg_tables where schemaname='public'";
        PreparedStatement pstm=conn.prepareStatement(sql);
        return pstm.executeQuery();
    }

    //获取表中所有字段名
    public ResultSet getColumnsName(Connection conn,String tableName) throws Exception{
        String sql="select column_name,data_type from information_schema.columns where table_schema='public'" +
                " and table_name=?";
        PreparedStatement pstm=conn.prepareStatement(sql);
        pstm.setString(1,tableName);
        return pstm.executeQuery();
    }

    //获取数据表中的所有数据
    public ResultSet listData(Connection conn,String tableName) throws Exception{
        String sql="select * from "+tableName;
        PreparedStatement pstm=conn.prepareStatement(sql);
        return pstm.executeQuery();
    }

    //删除
    public int delData(Connection conn, String tableName, int id) throws Exception{
        String sql="delete from "+tableName+" where id=?";
        PreparedStatement pstm=conn.prepareStatement(sql);
        pstm.setInt(1,id);
        return pstm.executeUpdate();
    }

    //添加
    public int AddData(Connection conn,String tableName,ArrayList<String> columnName,ArrayList<String> columnType,ArrayList<String> data) throws Exception{
        StringBuilder stringBuilder=new StringBuilder("Insert into "+tableName+"(");
        int i;
        int geomIndex=-1;//记录gemo字段所在下标
        for (i = 0; i < columnName.size(); i++) {
            if(columnName.get(i).equalsIgnoreCase("geom")){
                geomIndex=i;
            }
            stringBuilder.append(columnName.get(i)).append(",");//添加第i个字段名
        }
        stringBuilder.setLength(stringBuilder.length()-1);//去除最后一个逗号
        stringBuilder.append(") ").append("VALUES(");
        for (int j = 0; j < i; j++) {
            if(j==geomIndex){
                stringBuilder.append("st_geomfromtext(?),");//geom字段特殊处理
            }
            else {
                stringBuilder.append("?,");
            }
        }
        stringBuilder.setLength(stringBuilder.length()-1);//去除最后一个逗号
        stringBuilder.append(")");
        String sql=stringBuilder.toString();
        PreparedStatement pstm=conn.prepareStatement(sql);
        //设置数据
        for (int i1 = 0; i1 < data.size(); i1++) {
            //添加int数据
            if(columnType.get(i1).contains("int")){
                int n;
                if(data.get(i1).isEmpty()){
                    n=0;
                }
                else {
                    n=new Integer(data.get(i1).trim());
                }
                pstm.setInt(i1+1,n);
            }
            //添加字符串类型数据
            else {
                pstm.setString(i1+1, data.get(i1));
            }
        }
        return pstm.executeUpdate();
    }

    //修改
    public int Updata(Connection conn, String tableName,ArrayList<String> columnName,ArrayList<String> columnType, ArrayList<String> data) throws Exception{
        StringBuilder stringBuilder=new StringBuilder("Update "+tableName+" set");
        int idIndex = 0;//id不可修改
        int geomIndex=0;//geom不可修改
        for (int i = 0; i < columnName.size(); i++) {
            if(columnName.get(i).equalsIgnoreCase("id")){
                idIndex=i;
                continue;
            }
            else if(columnName.get(i).equalsIgnoreCase("geom")){
                geomIndex=i;
                continue;
            }
            if(data.get(i).isEmpty()){
                continue;
            }
            stringBuilder.append(" ").append(columnName.get(i)).append("=?").append(",");
        }

        stringBuilder.setLength(stringBuilder.length()-1);//去逗号
        stringBuilder.append(" where id=?");
        String sql=stringBuilder.toString();
        PreparedStatement pstm=conn.prepareStatement(sql);
        //设置数据
        int j=1;
        for (int i = 0; i < data.size(); i++) {
            if(i==idIndex||i==geomIndex)
                continue;
            if(data.get(i).isEmpty())
                continue;
            //int
            if(columnType.get(i).contains("int")){
                int n;
                n=new Integer(data.get(i).trim());
                pstm.setInt(j,n);
            }
            //字符串
            else {
                pstm.setString(j, data.get(i));
            }
            j++;
        }
        pstm.setInt(j,new Integer(data.get(idIndex)));//设置id=？
        return pstm.executeUpdate();
    }
}
